﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using MandalaySoft.Common;
using System.Data.SqlClient;
using System.Data;

namespace SIMS.BL
{
    public class SchoolDB
    {
        MyDatabase _db = new MyDatabase();

        public DataTable GetSchoolNameList(string filter, bool IsFullName, out MyError error)
        {
            DataTable _dt = new DataTable("tblSchoolName");
            string strSQLStatement = "SELECT SchoolID";
            if (IsFullName)
            {
                strSQLStatement += ", SchoolName ";
            }
            else
            {
                strSQLStatement += ", ShortName As SchoolName ";
            }
            strSQLStatement += "FROM tblSchool ";

            if (filter != string.Empty)
            {
                strSQLStatement += "WHERE " + filter;
            }
            else
            {
                strSQLStatement += "WHERE IsActive = 1 ";
            }

            _dt = _db.GetDataTable(strSQLStatement, CommandType.Text, new SortedList(), out error);
            return _dt;
        }

        public School GetItem(int SchoolID)
        {
            School item = new School();

            SortedList _paraList = new SortedList();
            _paraList.Add("@SchoolID", SchoolID);

            MyError _error = new MyError();
            SqlDataReader _reader = _db.GetSqlDataReader("sprocSchoolSelectSingleItem", System.Data.CommandType.StoredProcedure, _paraList, out _error);
            item.Error = _error;

            if (_error.ErrorNo == 0 && _reader.Read())
            {
                item = FillData(_reader, item);
            }

            return item;
        }

        public DataTable GetList()
        {
            DataTable _dtRet = new DataTable();
            SortedList _paraList = new SortedList();

            MyError _error = new MyError();
            _dtRet = _db.GetDataTable("sprocSchoolSelectItemList", System.Data.CommandType.StoredProcedure, _paraList, out _error);
            return _dtRet;
        }

        public School Save(School item)
        {
            try
            {
                SortedList _paraList = new SortedList();
                _paraList.Add("@SchoolID", item.SchoolID);
                _paraList.Add("@SchoolName", item.SchoolName);
                _paraList.Add("@ShortName", item.ShortName);
                _paraList.Add("@Address", item.Address);
                _paraList.Add("@City", item.City);
                _paraList.Add("@State", item.State);
                _paraList.Add("@PostalCode", item.PostalCode);
                _paraList.Add("@Telephone", item.Telephone);
                _paraList.Add("@PrincipleName", item.PrincipleName);
                _paraList.Add("@FaxLine", item.FaxLine);
                _paraList.Add("@EmailAddress", item.EmailAddress);
                _paraList.Add("@WebsiteUrl", item.WebsiteUrl);
                _paraList.Add("@BaseGradeScale", item.BaseGradeScale);
                _paraList.Add("@SchoolLogoUrl", item.SchoolLogoUrl);
                _paraList.Add("@IsActive", item.IsActive);
                _paraList.Add("@CreatedBy", item.CreatedBy);
                _paraList.Add("@ModifiedBy", item.SchoolID);

                MyError _error = new MyError();
                Int64 _retValue = _db.Execute("sprocSchoolInsertUpdateSingleItem", System.Data.CommandType.StoredProcedure, _paraList, out _error);
                item.Error = _error;

                if (_retValue > 0)
                {
                    item.SchoolID = Convert.ToInt32(_retValue);
                    item.Error.ErrorMessage = "Saved successfully!";
                }
                else
                {
                    if (_error.ErrorNo == 0)
                    {
                        item.Error.ErrorNo = 10002;
                        item.Error.ErrorLocation = "School";
                        item.Error.ErrorMessage = "No row affected.";
                    }
                }

            }
            catch (Exception Ex)
            {
                item.Error.ErrorNo = 10001;
                item.Error.ErrorLocation = "School";
                item.Error.ErrorMessage = Ex.Message;
                item.Error.ErrorStack = Ex.StackTrace;

                MyError.CreateExceptionLog(Ex, "School");
            }

            return item;
        }

        private School FillData(IDataReader aRow, School item)
        {
            try
            {
                item = new School();
                item.SchoolID = aRow.GetInt32(aRow.GetOrdinal("SchoolID"));
                item.SchoolName = aRow.GetString(aRow.GetOrdinal("SchoolName"));
                item.ShortName = aRow.GetString(aRow.GetOrdinal("ShortName"));
                item.Address = aRow.GetString(aRow.GetOrdinal("Address"));
                item.City = aRow.GetString(aRow.GetOrdinal("City"));
                if (aRow["BaseGradeScale"] != DBNull.Value)
                {
                    item.BaseGradeScale = aRow.GetDecimal(aRow.GetOrdinal("BaseGradeScale"));
                }
                if (aRow["State"] != DBNull.Value)
                {
                    item.State = aRow.GetString(aRow.GetOrdinal("State"));
                }
                if (aRow["PostalCode"] != DBNull.Value)
                {
                    item.PostalCode = aRow.GetString(aRow.GetOrdinal("PostalCode"));
                }
                if (aRow["Telephone"] != DBNull.Value)
                {
                    item.Telephone = aRow.GetString(aRow.GetOrdinal("Telephone"));
                }
                if (aRow["PrincipleName"] != DBNull.Value)
                {
                    item.PrincipleName = aRow.GetString(aRow.GetOrdinal("PrincipleName"));
                }
                if (aRow["FaxLine"] != DBNull.Value)
                {
                    item.FaxLine = aRow.GetString(aRow.GetOrdinal("FaxLine"));
                }
                if (aRow["EmailAddress"] != DBNull.Value)
                {
                    item.EmailAddress = aRow.GetString(aRow.GetOrdinal("EmailAddress"));
                }
                if (aRow["WebsiteUrl"] != DBNull.Value)
                {
                    item.WebsiteUrl = aRow.GetString(aRow.GetOrdinal("WebsiteUrl"));
                }
                if (aRow["SchoolLogoUrl"] != DBNull.Value)
                {
                    item.SchoolLogoUrl = aRow.GetString(aRow.GetOrdinal("SchoolLogoUrl"));
                }
                item.IsActive = aRow.GetBoolean(aRow.GetOrdinal("IsActive"));
                item.CreatedBy = aRow.GetInt32(aRow.GetOrdinal("CreatedBy"));
                if (_db.ColumnExists(aRow, "CreatedByName") && aRow["CreatedByName"] != DBNull.Value)
                {
                    item.CreatedByName = aRow.GetString(aRow.GetOrdinal("CreatedByName"));
                }
                item.CreatedDate = aRow.GetDateTime(aRow.GetOrdinal("CreatedDate"));
                item.ModifiedBy = aRow.GetInt32(aRow.GetOrdinal("ModifiedBy"));
                if (_db.ColumnExists(aRow, "ModifiedByName") && aRow["ModifiedByName"] != DBNull.Value)
                {
                    item.ModifiedByName = aRow.GetString(aRow.GetOrdinal("ModifiedByName"));
                }
                item.ModifiedDate = aRow.GetDateTime(aRow.GetOrdinal("ModifiedDate"));
            }
            catch (Exception Ex)
            {
                item.Error.ErrorNo = 10003;
                item.Error.ErrorLocation = "School";
                item.Error.ErrorMessage = Ex.Message;
                item.Error.ErrorStack = Ex.StackTrace;

                MyError.CreateExceptionLog(Ex, "School");
            }

            return item;
        }
    }
}
